package org.codefilarete.stalactite.query.builder;

import javax.annotation.Nullable;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import org.codefilarete.stalactite.query.builder.FunctionSQLBuilderFactory.FunctionSQLBuilder;
import org.codefilarete.stalactite.query.model.ConditionalOperator;
import org.codefilarete.stalactite.query.api.Fromable;
import org.codefilarete.stalactite.query.model.Query;
import org.codefilarete.stalactite.query.api.QueryStatement;
import org.codefilarete.stalactite.query.api.Selectable;
import org.codefilarete.stalactite.query.model.Union;
import org.codefilarete.stalactite.query.model.ValuedVariable;
import org.codefilarete.stalactite.query.api.Variable;
import org.codefilarete.stalactite.query.model.operator.Between;
import org.codefilarete.stalactite.query.model.operator.Between.Interval;
import org.codefilarete.stalactite.query.model.operator.Equals;
import org.codefilarete.stalactite.query.model.operator.Greater;
import org.codefilarete.stalactite.query.model.operator.In;
import org.codefilarete.stalactite.query.model.operator.InSubQuery;
import org.codefilarete.stalactite.query.model.operator.IsNull;
import org.codefilarete.stalactite.query.model.operator.Lesser;
import org.codefilarete.stalactite.query.model.operator.Like;
import org.codefilarete.stalactite.query.model.operator.SQLFunction;
import org.codefilarete.stalactite.query.model.operator.TupleIn;
import org.codefilarete.stalactite.sql.ddl.structure.Column;
import org.codefilarete.tool.Reflections;
import org.codefilarete.tool.VisibleForTesting;

/**
 * A class made to print a {@link ConditionalOperator}
 * 
 * @author Guillaume Mary
 */
public class OperatorSQLBuilderFactory {
	
	public OperatorSQLBuilderFactory() {
	}
	
	/**
	 * Constructs a {@link OperatorSQLBuilder} for printing operators as SQL.
	 *
	 * @param functionSQLBuilder the builder for SQL function printer
	 * @param querySQLBuilderFactory a factory for printing instances of {@link QueryStatement}s used in subqueries for "in" operator
	 * @return an instance of {@link OperatorSQLBuilder}
	 */
	public OperatorSQLBuilder operatorSQLBuilder(FunctionSQLBuilder functionSQLBuilder, QuerySQLBuilderFactory querySQLBuilderFactory) {
		return new OperatorSQLBuilder(functionSQLBuilder, querySQLBuilderFactory);
	}
	
	public static class OperatorSQLBuilder {
		
		private final FunctionSQLBuilder functionSQLBuilder;
		private final QuerySQLBuilderFactory querySQLBuilderFactory;
		
		/**
		 * Constructs a {@link OperatorSQLBuilder} for building operators.
		 *
		 * @param functionSQLBuilder the builder for function SQL
		 * @param querySQLBuilderFactory a factory for printing instances of {@link QueryStatement}s used in subqueries for "in" operator
		 */
		public OperatorSQLBuilder(FunctionSQLBuilder functionSQLBuilder, QuerySQLBuilderFactory querySQLBuilderFactory) {
			this.functionSQLBuilder = functionSQLBuilder;
			this.querySQLBuilderFactory = querySQLBuilderFactory;
		}
		
		/**
		 * Main entry point
		 */
		public void cat(ConditionalOperator operator, SQLAppender sql) {
			if (operator instanceof TupleIn) {
				catTupledIn((TupleIn) operator, sql);
			} else {
				cat(null, operator, sql);
			}
		}
		
		public <V> void cat(Selectable<V> column, ConditionalOperator<?, V> operator, SQLAppender sql) {
			if (operator.isNull()) {
				catNullValue(operator.isNot(), sql);
			} else {
				// ugly way of dispatching concatenation, can't find a better way without heaving classes or struggling with single responsibility design
				if (operator instanceof Equals) {
					catEquals((Equals<V>) operator, sql, column);
				} else if (operator instanceof Lesser) {
					catLower((Lesser<V>) operator, sql, column);
				} else if (operator instanceof Greater) {
					catGreater((Greater<V>) operator, sql, column);
				} else if (operator instanceof Between) {
					catBetween((Between<V>) operator, sql, column);
				} else if (operator instanceof In) {
					catIn((In<V>) operator, sql, column);
				} else if (operator instanceof Like) {
					catLike((Like) operator, sql, column);
				} else if (operator instanceof IsNull) {
					catIsNull((IsNull) operator, sql);
				} else if (operator instanceof InSubQuery) {
					catInSubQuery((InSubQuery) operator, sql);
				} else {
					throw new UnsupportedOperationException("Operator " + Reflections.toString(operator.getClass()) + " is not implemented");
				}
			}
		}
		
		void catNullValue(boolean not, SQLAppender sql) {
			// "= NULL" is incorrect and will return no result (answer from Internet) and should be replaced by "is null"
			sql.cat("is").catIf(not, " not").cat(" null");
		}
		
		void catIsNull(IsNull<?> isNull, SQLAppender sql) {
			catNullValue(isNull.isNot(), sql);
		}
		
		void catLike(Like like, SQLAppender sql, Selectable<?> column) {
			sql.catIf(like.isNot(), "not ").cat("like ");
			LikePatternAppender likePatternAppender = new LikePatternAppender(like, sql);
			if (like.getValue() instanceof ValuedVariable) {
				Object value = ((ValuedVariable<?>) like.getValue()).getValue();
				if (value instanceof SQLFunction) {
					functionSQLBuilder.cat((SQLFunction) value, likePatternAppender);
				} else {
					likePatternAppender.catValue(column, like.getValue());
				}
			} else {
				likePatternAppender.catValue(column, like.getValue());
			}
		}
		
		<V> void catIn(In<V> in, SQLAppender sql, Selectable<V> column) {
			// we take the collection into account: iterating over it to cat all values
			Variable<Iterable<V>> value = in.getValue();
			sql.catIf(in.isNot(), "not ").cat("in (");
			catInValue(value, sql, column);
			sql.cat(")");
		}
		
		void catTupledIn(TupleIn in, SQLAppender sql) {
			// we take the collection into account: iterating over it to cat all values
			Column[] columns = in.getColumns();
			sql.catIf(in.isNot(), "not ");
			sql.cat("(");
			Iterator<Column> columnIterator = Arrays.stream(columns).iterator();
			while (columnIterator.hasNext()) {
				sql.catColumn(columnIterator.next()).catIf(columnIterator.hasNext(), ", ");
			}
			sql.cat(")");
			
			sql.cat(" in (");
			Variable<List<Object[]>> value = in.getValue();
			if (value instanceof ValuedVariable<?>) {
				List<Object[]> values = ((ValuedVariable<List<Object[]>>) value).getValue();
				if (values == null) {
					for (int i = 0, columnCount = columns.length; i < columnCount; i++) {
						sql.catValue(columns[i], null).catIf(i < columnCount - 1, ", ");
					}
				} else {
					Iterator<Object[]> valuesIterator = values.iterator();
					while (valuesIterator.hasNext()) {
						Object[] vals = valuesIterator.next();
						sql.cat("(");
						for (int i = 0, columnCount = columns.length; i < columnCount; i++) {
							sql.catValue(columns[i], vals[i]).catIf(i < columnCount - 1, ", ");
						}
						sql.cat(")").catIf(valuesIterator.hasNext(), ", ");
					}
				}
			}
			sql.cat(")");
		}
		
		<V> void catInSubQuery(InSubQuery<V> in, SQLAppender sql) {
			sql.catIf(in.isNot(), "not ").cat("in (");
			QueryStatement query = in.getQuery();
			QuerySQLBuilderFactory.QueryStatementSQLBuilder queryBuilder;
			if (query instanceof Query) {
				queryBuilder = querySQLBuilderFactory.queryBuilder((Query) query);
			} else if (query instanceof Union) {
				queryBuilder = querySQLBuilderFactory.unionBuilder((Union) query);
			} else {
				throw new IllegalArgumentException("Unsupported query type: " + Reflections.toString(query.getClass()));
			}
			queryBuilder.appendTo(sql);
			sql.cat(")");
		}
		
		/**
		 * Appends {@link Iterable} values to the given appender. Essentially done for "in" operator
		 *
		 * @param sql the appender
		 * @param column
		 * @param value the iterable to be appended, not null (but may be empty, this method doesn't care)
		 */
		private <V> void catInValue(Variable<Iterable<V>> value, SQLAppender sql, Selectable<V> column) {
			// appending values (separated by a comma, boilerplate code)
			boolean isFirst = true;
			if (value instanceof ValuedVariable) {
				for (Object v : ((ValuedVariable<Iterable<V>>) value).getValue()) {
					if (!isFirst) {
						sql.cat(", ");
					} else {
						isFirst = false;
					}
					if (v instanceof SQLFunction) {
						functionSQLBuilder.cat((SQLFunction) v, sql);
					} else {
						sql.catValue(column, v);
					}
				}
			} else {
				// we know this cast is wrong : value is Variable<Iterable<V>>. But adding a signature for it is too much work for few usage
				sql.catValue(column, value);
			}
		}
		
		<V> void catBetween(Between<V> between, SQLAppender sql, Selectable<V> column) {
			Variable<Interval<V>> value = between.getValue();
			if (value instanceof ValuedVariable) {
				Interval<V> interval = ((ValuedVariable<Interval<V>>) value).getValue();
				if (interval.getValue1() == null) {
					sql.cat(between.isNot() ? ">= " : "< ").catValue(column, interval.getValue2());
				} else if (interval.getValue2() == null) {
					sql.cat(between.isNot() ? "<= " : "> ").catValue(column, interval.getValue1());
				} else {
					sql.catIf(between.isNot(), "not ").cat("between ")
							.catValue(column, interval.getValue1())
							.cat(" and ")
							.catValue(column, interval.getValue2());
				}
			}
		}
		
		@SuppressWarnings("squid:S3358")	// we can afford nesting ternary operators here, not so complex to understand 
		<V> void catGreater(Greater<V> greater, SQLAppender sql, Selectable<V> column) {
			sql.cat(greater.isNot()
							? (greater.isEquals() ? "< " : "<= ")
							: (greater.isEquals() ? ">= " : "> "));
			catValue(column, greater.getValue(), sql);
		}
		
		@SuppressWarnings("squid:S3358")	// we can afford nesting ternary operators here, not so complex to understand
		<V> void catLower(Lesser<V> lesser, SQLAppender sql, Selectable<V> column) {
			sql.cat(lesser.isNot()
							? (lesser.isEquals() ? "> " : ">= ")
							: (lesser.isEquals() ? "<= " : "< "));
			catValue(column, lesser.getValue(), sql);
		}
		
		<V> void catEquals(Equals<V> equals, SQLAppender sql, Selectable<V> column) {
			sql.catIf(equals.isNot(), "!").cat("= ");
			catValue(column, equals.getValue(), sql);
		}
		
		<V> void catValue(@Nullable Selectable<V> column, Variable<V> variable, SQLAppender sql) {
			if (variable instanceof ValuedVariable) {
				V value = ((ValuedVariable<V>) variable).getValue();
				if (value instanceof SQLFunction) {
					functionSQLBuilder.cat((SQLFunction<?, ?>) value, sql);
				} else {
					sql.catValue(column, variable);
				}
			} else {
				sql.catValue(column, variable);
			}
		}
		
		/**
		 * Adds leading and ending "%" while appending Like values.
		 * Made for cases composed Like with function as argument (lower, upper, ...) because they are not aware of being embedded in a Like operator.
		 * 
		 * @author Guillaume Mary
		 */
		@VisibleForTesting
		static class LikePatternAppender implements SQLAppender {
			private final Like<?> like;
			private final SQLAppender sql;
			
			@VisibleForTesting
			LikePatternAppender(Like<?> like, SQLAppender sql) {
				this.like = like;
				this.sql = sql;
			}
			
			@Override
			public <V> SQLAppender catValue(@Nullable Selectable<V> column, Object variable) {
				if (variable instanceof ValuedVariable) {
					V value = ((ValuedVariable<V>) variable).getValue();
					if (value instanceof CharSequence) {
						sql.catValue((Selectable<CharSequence>) column, addWildcards((CharSequence) value));
					} else {
						sql.catValue(column, variable);
					}
				} else {
					sql.catValue(column, variable);
				}
				return this;
			}
			
			private CharSequence addWildcards(CharSequence effectiveValue) {
				if (like.withLeadingStar()) {
					effectiveValue = "%" + effectiveValue;
				}
				if (like.withEndingStar()) {
					effectiveValue += "%";
				}
				return effectiveValue;
			}
			
			@Override
			public SQLAppender catValue(Object value) {
				if (value instanceof ValuedVariable) {
					value = ((ValuedVariable<?>) value).getValue();
				}
				if (value instanceof Selectable) {
					// unwrapping raw Selectable
					sql.catValue(addWildcards(((Selectable<CharSequence>) value).getExpression()));
				} else if (value instanceof CharSequence) {
					sql.catValue(addWildcards((CharSequence) value));
				} else {
					throw new UnsupportedOperationException("Appending '" + value + "'"
							+ (value == null ? "" : " (type " + Reflections.toString(value.getClass()) + ")") + " is not supported");
				}
				return this;
			}
			
			@Override
			public SQLAppender cat(String s, String... ss) {
				sql.cat(s, ss);
				return this;
			}
			
			@Override
			public SQLAppender catColumn(Selectable<?> column) {
				sql.catColumn(column);
				return this;
			}
			
			@Override
			public SQLAppender catTable(Fromable table) {
				sql.catTable(table);
				return this;
			}
			
			@Override
			public SQLAppender removeLastChars(int length) {
				sql.removeLastChars(length);
				return this;
			}
			
			@Override
			public String getSQL() {
				return sql.getSQL();
			}
			
			@Override
			public SubSQLAppender newSubPart(DMLNameProvider dmlNameProvider) {
				SQLAppender self = this;
				return new DefaultSubSQLAppender(new LikePatternAppender(this.like, this.sql.newSubPart(dmlNameProvider))) {
					@Override
					public SQLAppender close() {
						// nothing special;
						return self;
					}
				};
			}
		}
	}
}
